package com.alianzamedica.servlets;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Iterator;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.objectsearch.sqlsearch.ObjectSearch;
import org.objectsearch.web.tools.Converter;
import org.w3c.dom.Document;

import com.alianzamedica.businessobject.MedicReportObject;
import com.alianzamedica.connection.ConnectionImpl;
import com.alianzamedica.tools.Enviroment;

/**
 * @author Carlos
 * 
 */
public class ReportServlet extends HttpServlet {

	/**
	 * 
	 */
	private static final long serialVersionUID = 3552999035345561032L;

	/**
	 * implementacion del procesamiento de las peticiones get y post.
	 * 
	 * @param request
	 *            servlet request.
	 * @param response
	 *            servlet response.
	 * @throws ServletException
	 *             si ocurre error del servlet.
	 * @throws IOException
	 *             si ocurre error de lectura.
	 */
	protected void processRequest(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		ServletOutputStream out = response.getOutputStream();
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Content-disposition",
				"attachment;filename=Report.xls");

		try {
			HSSFWorkbook excelDoc = new HSSFWorkbook();
			HSSFSheet hoja1 = excelDoc.createSheet("reporte");
			Integer doctorId = Converter.string2Integer(request
					.getParameter("medicId"));
			if (doctorId == null) {
				out.close();
				return;
			}
			HSSFRow fila0 = hoja1.createRow(0);
			fila0.createCell(0).setCellValue("Nombre");
			fila0.createCell(1).setCellValue("Universidad");
			fila0.createCell(2).setCellValue("Especialidad");
			fila0.createCell(3).setCellValue("Cedula");
			fila0.createCell(4).setCellValue("Certificacion");
			fila0.createCell(5).setCellValue("Fecha");
			fila0.createCell(6).setCellValue("Nombre");
			fila0.createCell(7).setCellValue("Apellidos");
			fila0.createCell(8).setCellValue("Edad");
			fila0.createCell(9).setCellValue("Diagnostico");
			fila0.createCell(10).setCellValue("Genero");
			fila0.createCell(11).setCellValue("Peso");
			ArrayList<MedicReportObject> medicReports = this
					.getReport(doctorId);
			Iterator<MedicReportObject> iterator = medicReports.iterator();
			int rownum = 2;
			while (iterator.hasNext()) {
				MedicReportObject medicReportObject = (MedicReportObject) iterator
						.next();
				HSSFRow fila1 = hoja1.createRow(rownum);

				HSSFCell celda1 = fila1.createCell(0);
				celda1.setCellValue(object2String(medicReportObject
						.getFullName()));
				HSSFCell celda2 = fila1.createCell(1);
				celda2.setCellValue(object2String(medicReportObject
						.getUniversity()));
				HSSFCell celda3 = fila1.createCell(2);
				celda3.setCellValue(object2String(medicReportObject
						.getSpeciality()));
				HSSFCell celda4 = fila1.createCell(3);
				celda4.setCellValue(object2String(medicReportObject.getCell()));
				HSSFCell celda5 = fila1.createCell(4);
				celda5.setCellValue(object2String(medicReportObject
						.getCertification()));
				HSSFCell celda6 = fila1.createCell(5);
				celda6.setCellValue(object2String(medicReportObject.getDate()));
				fila1.createCell(6).setCellValue(
						object2String(medicReportObject.getFirstName()));
				fila1.createCell(7).setCellValue(
						object2String(medicReportObject.getLastName()));
				HSSFCell celda7 = fila1.createCell(8);
				celda7.setCellValue(object2String(medicReportObject.getAge()));
				HSSFCell celda8 = fila1.createCell(9);
				celda8.setCellValue(object2String(medicReportObject
						.getDiagnostic()));
				HSSFCell celda9 = fila1.createCell(10);
				celda9.setCellValue(object2String(medicReportObject.getSex()));
				HSSFCell celda10 = fila1.createCell(11);
				celda10.setCellValue(object2String(medicReportObject
						.getWeight()));

				rownum++;
			}

			// HSSFCell celda1 = fila1.createCell(0);
			// celda1.setCellValue("1");

			excelDoc.write(out);
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				out.close();
			} catch (Exception e) {
			}
		}
	}

	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		processRequest(req, resp);
	}

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		processRequest(req, resp);
	}

	/**
	 * Obtiene el reporte por Medico.
	 * 
	 * @param doctorId
	 *            id del doctor.
	 * @return lista de reportes por medico.
	 * @throws Exception
	 *             si ocurre error.
	 */
	@SuppressWarnings("unchecked")
	public ArrayList<MedicReportObject> getReport(int doctorId)
			throws Exception {

		ArrayList<MedicReportObject> list = null;

		String sql = "select DOCTOR_FULLNAME,DOCTOR_UNIVERSITY,DOCTOR_SPECIALITY,DOCTOR_CELL,"
				+ " DOCTOR_CERTIFICATION_NUMBER,PRESCRIPTION_NAME,"
				+ " PRESCRIPTION_DATE  PRESCRIPTION_DATE ,PATIENT_FIRSTNAME,"
				+ " PATIENT_LASTNAME, PATIENT_AGE,PATIENT_DIAGNOSTIC,PATIENT_SEX,PATIENT_WEIGHT "
				+ " from prescription"
				+ " join patient"
				+ " on ( prescription.PATIENT_ID  = patient.PATIENT_ID )"
				+ " join doctor"
				+ " on ( doctor.DOCTOR_ID = prescription.DOCTOR_ID )"
				+ " where prescription.DOCTOR_ID = ? ";
		PreparedStatement psmt = null;
		ResultSet rs = null;
		ConnectionImpl impl = new ConnectionImpl();
		Enviroment env = Enviroment.getInstance();
		Document doc = env.getDocument();
		try {
			ObjectSearch search = new ObjectSearch(doc, ConnectionImpl.class
					.getName());
			impl.open();
			Connection con = impl.getConnection();
			psmt = con.prepareStatement(sql);
			psmt.setInt(1, doctorId);
			list = search.customQuery(psmt, con, MedicReportObject.class);
		} finally {
			impl.closeGracefully(psmt);
			impl.closeGracefully(rs);
			try {
				impl.close();
			} catch (Exception e) {
			}
		}
		return list;
	}

	private String object2String(Object object) {
		if (object == null) {
			return "";
		} else {
			return object.toString();
		}

	}

}
